package cn.ainit.bs.report;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.ainit.bs.repList.entity.RepListButtons;
import cn.ainit.bs.repList.entity.RepListCondition;
import cn.ainit.bs.repList.entity.RepListDataStore;
import cn.ainit.bs.repList.entity.RepViewType;
import cn.ainit.bs.report.entity.ReportBar;
import cn.ainit.bs.report.entity.ReportDataList;
import cn.ainit.bs.report.entity.ReportBarReP;
import cn.ainit.bs.report.entity.ReportLineReP;
import cn.ainit.bs.report.entity.ReportPie;
import cn.ainit.bs.report.entity.ReportPieReP;
import cn.ainit.sys.query.entity.DataGridReceiveParams;
import cn.ainit.sys.query.entity.QueryJson;

@Controller
@RequestMapping("/report")
public class ReportController {
	ReportService reportService;

	@Resource
	public void setReportService(ReportService reportService) {
		this.reportService = reportService;
	}

	@RequestMapping("/loadBarData")
	@ResponseBody
	public ReportDataList loadData(String reId,HttpServletRequest request){
		ReportDataList reportDataList = new ReportDataList();
		try {
			if (reId != null && !reId.equals("")) {
				ReportBar rObj = reportService.findRCOById(reId);
				if (rObj != null) {
					//设置返回信息
					reportDataList.setMessage("success");
					//设置报表title
					reportDataList.setTitle(rObj.getTitle());
					List colors = new ArrayList<String>();
					//'Gradient(#fe783e:#EC561B:#F59F7D)'
					String[] tmpStrArr = rObj.getBarColors().split(";");
					for (int i = 0; i < tmpStrArr.length; i++) {
						colors.add(tmpStrArr[i]);
					}
					//设置颜色
					reportDataList.setColors(colors);
					//设置Y轴名
					reportDataList.setYname(rObj.getYname());
					//设置X轴名
					reportDataList.setXname(rObj.getXname());
					//根据sql查数据
					String sql = "";
					if (rObj.getOrderColumnName()!=null && !rObj.getOrderColumnName().equals("")) {   //orderColumnName  获得是查询语句的列名
						sql = "select * from ("+rObj.getSqlText()+") TMP order by "+rObj.getOrderColumnName();
						if (rObj.getOrderByDesc().equals("T")) sql += " desc ";   //ORDER_BY_DESC是否进行倒叙排列
					}
					//设置数据
					reportDataList.setDataList(reportService.queryRpData(sql,rObj.getXshowCount())); 
					System.out.println(reportDataList.getDataList().size());
					//设置X轴字段集
					List xColumns = new ArrayList();
					tmpStrArr = rObj.getXcolumns().split(";");
					for (int i = 0; i < tmpStrArr.length; i++) {
						xColumns.add(tmpStrArr[i].toUpperCase());
					}
					reportDataList.setxColumnList(xColumns);
					//设置Y轴字段名
					reportDataList.setyColumnName(rObj.getYcolumnName().toUpperCase());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return reportDataList;
	}


	/****
	 * 得到列表(的dsId和要显示的按钮和视图分析的条件)
	 * @param reId
	 * @param request
	 * @return
	 * author:cff
	 */
	@RequestMapping("/loadListData")
	public String  loadListData(String dsId,HttpServletRequest request){
		ReportDataList reportDataList=new ReportDataList();
		String sql="";
		try{
			if (dsId != null && !dsId.equals("")) {
				//根据报表ID得到报表的按钮
				List<RepListButtons> reportSysButtons=reportService.getReportButns(dsId);
				//根据报表ID得到报表的条件
				List<RepListCondition> rcs=reportService.getReportCondition(dsId);

				reportDataList.getQueryJson().setButtons(reportSysButtons);
				reportDataList.setViewConditions(rcs);
				//根据报表ID得到报表对象
				RepListDataStore rObj = reportService.findByDsId(dsId);
				if(rObj!=null){
					//设置返回的信息
					reportDataList.setMessage("success");
					//设置报表的title
					reportDataList.setTitle(rObj.getTitle());
				}
				request.setAttribute("m", reportDataList);
				request.setAttribute("reId",dsId);
			}else{
				throw new RuntimeException("报表DSID为空");
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return "/sysReport/report_list";
	}


	/***
	 * 加载查询条件界面字段
	 * @param colId
	 * @param type
	 * @param flag
	 * @param request
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/loadColumns")
	@ResponseBody
	public List loadColumns(String dsId,String type,String flag,HttpServletRequest request) throws Exception{
		List columns = new ArrayList();
		try {
			columns = reportService.getReportCols(dsId, type,flag);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return columns;
	}

	/***
	 * 加载查询条件和筛选条件界面字段
	 * @param colId
	 * @param type
	 * @param flag
	 * @param request
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("getViewCols")
	@ResponseBody
	public List getViewCols(String dsId, String config, String type,String flag,HttpServletRequest request) throws Exception{
		List columns = new ArrayList();
		try {
			columns = reportService.getReportCol(dsId,config,type,flag);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return columns;
	}


	/**
	 * 返回dataGrid中的数据
	 * @author Xuzq 2014-01-20
	 * @param dataGParams
	 * @param dsId
	 * @param request
	 * @return
	 */
	@RequestMapping("/loadFullData")
	@ResponseBody
	public QueryJson loadFullData(DataGridReceiveParams dataGParams,String dsId,String config,HttpServletRequest request){
		QueryJson queryJson=new QueryJson();
		try{
			RepListDataStore repListDataStore=reportService.findByDsId(dsId);
			StringBuffer sqlBuffer = new StringBuffer("select * from ("+repListDataStore.getSqlText()+") QUERYTMP where 1 = 1  ");
			if (repListDataStore != null) {
				if(config!=null&&config!=""){
					String[] configS = config.split(";");
					String[] tmpValue;
					for (int i = 0; i < configS.length;i++) {
						tmpValue = configS[i].split("\\-");
						if (tmpValue.length == 2) {
							sqlBuffer.append(" AND "+tmpValue[0]+"='"+tmpValue[1]+"' ");
						}
					}
				}
				QueryJson tmpQJ = reportService.queryRowsBySqlPagination(sqlBuffer.toString(),dataGParams.getPage(),dataGParams.getRows());
				System.out.println(tmpQJ.getRows().size());
				queryJson.setRows(tmpQJ.getRows());
				queryJson.setTotal(tmpQJ.getTotal());
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/****
	 * 根据报表的筛选条件返回dataGrid中的数据
	 * @param dataGParams
	 * @param dsId
	 * @param config
	 * @param request
	 * @return
	 */
	@RequestMapping("/loadViewData")
	@ResponseBody
	public QueryJson loadViewData(DataGridReceiveParams dataGParams,String dsId,String config,HttpServletRequest request){
		QueryJson queryJson=new QueryJson();
		try{
			RepListDataStore repListDataStore=reportService.findByDsId(dsId);
			StringBuffer sqlBuffer = new StringBuffer("select * from ("+repListDataStore.getSqlText()+") QUERYTMP where 1 = 1  ");
			if(config!=null&&config!=""){
				RepViewType repViewType=reportService.getViewChoice(dsId,config);
				List<RepViewType> repViewTypes=reportService.getCommonData(dsId);
				sqlBuffer = new StringBuffer("select "+repViewType.getAlias());
				for (RepViewType it : repViewTypes) {
					sqlBuffer.append(","+it.getFucn()+"("+it.getColid()+") "+it.getAlias());
				}
				
				sqlBuffer.append(" from ("+repListDataStore.getSqlText()+") temp ");
				sqlBuffer.append("group by  temp."+repViewType.getAlias());
				QueryJson tmpQJ = reportService.queryRowsBySqlPagination(sqlBuffer.toString(),dataGParams.getPage(),dataGParams.getRows());
				System.out.println(tmpQJ.getRows().size());
				queryJson.setRows(tmpQJ.getRows());
				queryJson.setTotal(tmpQJ.getTotal());
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}
	
	
	/***
	 * 特殊查询时得到返回dataGrid中的数据列表
	 * @return
	 */
	@RequestMapping("/specialSelect")
	@ResponseBody
	public QueryJson specialSelect(DataGridReceiveParams dataGParams,String dsId,String specialConfig,HttpServletRequest request){
		QueryJson queryJson=new QueryJson();
		try{
			System.out.println("specialConfig:"+specialConfig);
			RepListDataStore repListDataStore=reportService.findByDsId(dsId);
			StringBuffer sqlBuffer = new StringBuffer("select * from ("+repListDataStore.getSqlText()+") QUERYTMP where 1 = 1  ");
			if (repListDataStore != null) {
				if(specialConfig!=null&&specialConfig!=""){
					sqlBuffer.append(" and ");
					String[] configS = specialConfig.split(";");  
					String[] tmpValue;
					for (int i = 0; i < configS.length;i++) {
						tmpValue = configS[i].split("\\-");
						sqlBuffer.append(" "+tmpValue[0]+" ");
						System.out.println("tmpValue[1]:"+tmpValue[1]);
						System.out.println("tmpValue[2]:"+tmpValue[2]);
						if(tmpValue[1].equals("%3D")){      //查询服务为等于
							tmpValue[1]="="+tmpValue[2];
						}else if(tmpValue[1].equals("!%3D")){  //查询模式为不等于
							tmpValue[1]="!="+tmpValue[2];
						}else if(tmpValue[1].equals("%25%25")){  //查询模式包含like
							tmpValue[1]=" like '%"+tmpValue[2]+"%'";
						}else if(tmpValue[1].equals("*%25")){  //查询模式以..开头   
							tmpValue[1]=" like '%"+tmpValue[2]+"%'";
						}else if(tmpValue[1].equals("%25*")){  //查询模式以..结尾
							tmpValue[1]=" like '"+tmpValue[2]+"%'";
						}else if(tmpValue[1].equals(">")){  //查询模式大于..
							tmpValue[1]=">"+tmpValue[2];
						}else if(tmpValue[1].equals("<")){  //查询模式小于..
							tmpValue[1]="<"+tmpValue[2];
						}else if(tmpValue[1].equals(">%3D")){  //查询模式大于等于..
							tmpValue[1]=">="+tmpValue[2];
						}else if(tmpValue[1].equals("<%3D")){  //查询模式小于等于..
							tmpValue[1]="<="+tmpValue[2];
						}else{
							tmpValue[1]=tmpValue[1]+tmpValue[2];
						}
						sqlBuffer.append(" "+tmpValue[1]);
						if (i!=configS.length-1) {
							sqlBuffer.append(" "+tmpValue[3]);
						}
					}
				}
				System.out.println("sqlBuffer:"+sqlBuffer.toString());
				QueryJson tmpQJ = reportService.queryRowsBySqlPagination(sqlBuffer.toString(),dataGParams.getPage(),dataGParams.getRows());
				System.out.println(tmpQJ.getRows().size());
				queryJson.setRows(tmpQJ.getRows());
				queryJson.setTotal(tmpQJ.getTotal());
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}
	
//*****V 2.0.0 图形报表************************************************************************************************
	
	/****************************************************
	 * 新版柱状图形报表
	 * ***************************************************/
	@RequestMapping("/a")
	@ResponseBody
	public ReportBarReP loadA(String config,String reid,HttpServletRequest request){
		ReportBarReP rdata = new ReportBarReP();
		try {
			if (reid != null && !"".equals(reid)) {
				/*****报表样式对象***/
				ReportBar chartObj = reportService.findBarByReId(reid);
				
				/******数据处理*****/
				RepListDataStore dataStore = reportService.findByDsId(reid);
				//条件处理
				//控制显示的条数
				StringBuffer sql = new StringBuffer("");
				
				sql.append(dataStore.getSqlText());
				Integer currentPage = 1;
				
				Integer pageSize = (chartObj.getXshowCount()==null && chartObj.getXshowCount()== 0)
									?10
									:chartObj.getXshowCount();
				//获取数据  List
				rdata.setDataList(reportService.loadRresult(sql,currentPage,pageSize));
				if (rdata.getDataList() == null ) {
					throw new RuntimeException("数据为空");
				}
				
				/*****报表样式对象***/
				//获取title
				rdata.setTitle(chartObj.getTitle());
				//获取x轴名
				rdata.setXname(chartObj.getXname());
				//获取x值
				if (chartObj.getXcolumns() != null) {
					rdata.setXcolumnList(chartObj.getXcolumns().split(";"));
				}
				//获取y轴名
				rdata.setYname(chartObj.getYname());
				//获取y值
				if (chartObj.getYcolumnName()!=null) {
					rdata.setYcolumnName(chartObj.getYcolumnName().split(";"));
				}
				//获取label值  = y值
				
				//获取bar的色块
				if (chartObj.getBarColors()!= null) {
					rdata.setColors(chartObj.getBarColors().split(";"));
				}
				
				//获取key值（每个bar的对象名）y轴的别名 例如：销量  退货量
				if (chartObj.getKeys()!=null) {
					rdata.setKeys(chartObj.getKeys().split(";"));
				}
				//获取颜色
			}else {
				throw new RuntimeException("DSID传入为空");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rdata;
	}
	
	/****************************************************
	 * 新版折线图形报表
	 * ***************************************************/
	@RequestMapping("/l")
	@ResponseBody
	public ReportLineReP loadL(String config,String reid,HttpServletRequest request){
		ReportLineReP rdata = new ReportLineReP();
		try {
			if (reid != null && !"".equals(reid)) {
				/*****返回报表样式处理***/
				ReportBar chartObj = reportService.findBarByReId(reid);
				
				/******数据处理*****/
				RepListDataStore dataStore = reportService.findByDsId(reid);
				//条件处理
				//控制显示的条数
				StringBuffer sql = new StringBuffer("");
				
				sql.append(dataStore.getSqlText());
				Integer currentPage = 1;
				Integer pageSize = (chartObj.getXshowCount()==null && chartObj.getXshowCount()== 0)
									?10
									:chartObj.getXshowCount();

				//获取数据  List
				rdata.setDataList(reportService.loadRresult(sql,currentPage,pageSize));
				if (rdata.getDataList() == null ) {
					throw new RuntimeException("数据为空");
				}
				
				/*****报表样式处理***/
				//获取title
				rdata.setTitle(chartObj.getTitle());
				//获取x轴名
				rdata.setXname(chartObj.getXname());
				//获取x值
				if (chartObj.getXcolumns() != null) {
					rdata.setXcolumnList(chartObj.getXcolumns().split(";"));
				}
				//获取y轴名
				rdata.setYname(chartObj.getYname());
				//获取y值
				if (chartObj.getYcolumnName()!=null) {
					rdata.setYcolumnName(chartObj.getYcolumnName().split(";"));
				}
				//获取label值  = y值
				
				//获取bar的色块
				if (chartObj.getBarColors()!= null) {
					rdata.setColors(chartObj.getBarColors().split(";"));
				}
				
				//获取key值（每个bar的对象名）y轴的别名 例如：销量  退货量
				if (chartObj.getKeys()!=null) {
					rdata.setKeys(chartObj.getKeys().split(";"));
				}
				//获取颜色
			}else {
				throw new RuntimeException("DSID传入为空");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rdata;
	}
	
	/****************************************************
	 * 新版折线图形报表
	 * ***************************************************/
	@RequestMapping("/p")
	@ResponseBody
	public ReportPieReP loadP(String config,String reid,HttpServletRequest request){
		ReportPieReP rdata = new ReportPieReP();
		try {
			if (reid != null && !"".equals(reid)) {
				/*****返回报表样式处理***/
				ReportPie chartObj = reportService.findPieByReId(reid);
				
				/******数据处理*****/
				RepListDataStore dataStore = reportService.findByDsId(reid);
				//条件处理
				//控制显示的条数
				StringBuffer sql = new StringBuffer("");
				
				sql.append(dataStore.getSqlText());
				Integer currentPage = 1;
				Integer pageSize = (chartObj.getXshowCount()==null && chartObj.getXshowCount()== 0)
									?10
									:chartObj.getXshowCount();

				//获取数据  List
				rdata.setDataList(reportService.loadRresult(sql,currentPage,pageSize));
				if (rdata.getDataList() == null ) {
					throw new RuntimeException("数据为空");
				}
				
				/*****报表样式处理***/
				//获取title
				rdata.setTitle(chartObj.getTitle());
				//获取x轴名
				rdata.setXname(chartObj.getXname());
				//获取x值
				if (chartObj.getXcolumns() != null) {
					rdata.setXcolumnList(chartObj.getXcolumns().split(";"));
				}
				//获取y轴名
				rdata.setYname(chartObj.getYname());
				//获取y值
				if (chartObj.getYcolumnName()!=null) {
					rdata.setYcolumnName(chartObj.getYcolumnName().split(";"));
				}
				//获取label值  = y值
				
				//获取bar的色块
				if (chartObj.getBarColors()!= null) {
					rdata.setColors(chartObj.getBarColors().split(";"));
				}
				
				//获取key值（每个bar的对象名）y轴的别名 例如：销量  退货量
				if (chartObj.getKeys()!=null) {
					rdata.setKeys(chartObj.getKeys().split(";"));
				}
				//获取颜色
			}else {
				throw new RuntimeException("DSID传入为空");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rdata;
	}
	
	
	
}
